package com.probiz.estore.system.dao.impl;

import java.util.ArrayList;
import java.util.List;

import com.probiz.estore.common.model.system.Region;
import com.probiz.estore.common.model.system.ShippingRate;
import com.probiz.estore.core.dao.impl.HibernateGenericDaoImpl;
import com.probiz.estore.core.model.BaseObject;
import com.probiz.estore.system.dao.RegionDao;
import com.probiz.estore.system.dao.ShippingRateDao;


/**
 * ShippingRate Data Access Object (DAO) implementation.
 * Developer introduced interfaces should be declared here. Won't get overwritten.
*/
public class ShippingRateDaoImpl extends HibernateGenericDaoImpl<ShippingRate> implements ShippingRateDao {
	
	protected void initFilterHqlMap() {
		filterHqlMap.put(BaseObject.FilterHqlName.DEFAULT, "select s from ShippingRate s ");	
		
	}
	private RegionDao regionDao;
	
	public void setRegionDao(RegionDao regionDao) {
		this.regionDao = regionDao;
	}

	/*
	 * (non-Javadoc)
	 * @see com.probiz.estore.system.dao.ShippingRateDao#getShippingRateByRegionId(java.lang.Integer)
	 */
	public List<ShippingRate> getShippingRateByRegionId(Integer regionId){
		Region region = regionDao.getById(regionId);
		if(null == region){
			return new ArrayList<ShippingRate>();
		}else{
			StringBuffer sql=new StringBuffer("from ShippingRate s");
			StringBuffer sb=new StringBuffer();
			List<Integer> regionIds=new ArrayList<Integer>();
			regionIds.add(region.getRegionId());
			Region parentRegion  = region.getParent();
			while(parentRegion != null){
				regionIds.add(parentRegion.getRegionId());
				parentRegion  = parentRegion.getParent();
			}
			for (Integer id : regionIds) {
				sb.append(",").append(id);
			}
			sb.setCharAt(0, '(');
			sb.append(')');
			sql.append(" where (s.region.regionId in ").append(sb).append(" or s.region.regionId in (select ri.region.regionId from RegionItem ri where ri.item.regionId in ").append(sb).append("))");
			sql.append(" and s.deleted=0 and s.shippingMethod.status=1");
			sql.append(" and s.shippingMethod.deleted=0 and s.shippingMethod.carrier.deleted=0 and s.shippingMethod.carrier.status=1");
			sql.append(" order by s.shippingMethod.shippingMethodId,s.shippingRateId");
			
			return findByHql(sql.toString());
		}
	}

}